package org.compiere.report;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import net.sf.jasperreports.engine.JRException;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.io.filefilter.WildcardFileFilter;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MUser;
import org.compiere.process.BalanceSheets;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class TurnoverStatementOfAccount extends SvrProcess {
	/** Current context	*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private Timestamp			Date_From = null;
	private Timestamp			Date_To = null;
	/**	Client Parameter					*/
	private int					AD_Client_ID = 0;
	/**	Org Parameter					*/
	private int					AD_Org_ID = 0;
	/**	Account Parameter				*/
	private int					Account_ID = 0;
	/**	BPartner Parameter				*/
	private int					C_BPartner_ID = 0;
	/** User Element 1 Parameter		*/
	private int					UserElement1_ID = 0;
	/** */
	private int 				FI_Form3_ID =0;
	/** */
	private int 				FI_Form3Explanation_ID = 0;
	/** */
	private int 				C_Charge_ID = 0;
	/** */
	private int 				HR_Department_ID =0;
	/** */
	private int 				n=0;
	/** */
	private int 				Subkonto1 = 0;
	/** */
	private int 				Subkonto2 = 0;
	/** */
	private int 				Subkonto3 = 0;
	/** */
	private boolean				Expand1 = false;
	/** */
	private boolean				Expand2 = false;
	/** */
	private boolean				Expand3 = false;
	/** */
	private static CLogger	s_log	= CLogger.getCLogger (BalanceSheets.class);
	/** */
	private String path = "";

	
	protected void prepare() {

		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Client_ID"))
				AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Account_ID"))
				Account_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Date_From"))
				Date_From = (Timestamp)para[i].getParameter();
			else if (name.equals("Date_To"))
				Date_To = (Timestamp)para[i].getParameter();
			else if (name.equals("Subkonto1"))
				Subkonto1 = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Subkonto2"))
				Subkonto2 = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Subkonto3"))
				Subkonto3 = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Expand1"))
				Expand1 = ((Boolean)para[i].getParameter());
			else if (name.equals("Expand2"))
				Expand2 = ((Boolean)para[i].getParameter());
			else if (name.equals("Expand3"))
				Expand3 = ((Boolean)para[i].getParameter());

			else if (name.equals("n"))
				n = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		//
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
			}
	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		int tt =n;
		Label label;
		Number number;
		DateTime date;
		// There are checks for a specified pattern
		String templateName = "tempOSV";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
//		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());

/*		
		StringBuffer fullPath = new StringBuffer(path);
		fullPath.append("Temporary Balance Sheets");
		fullPath.append(fileExtension);
*/
		AccValue[] acclines = getAccValues();
		
		//StringBuffer fullPath = new StringBuffer(getXlsName("Temporary Balance Sheets"));
		//pattern for date
	    SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
	    //---
	    String titleName = new Timestamp(System.currentTimeMillis()).toString();
	    StringBuffer fullPath = new StringBuffer(getXlsName("осв_"+acclines[0].getAccvalue()
	    		+"_"+titleName.substring(0, 10)+"_"+titleName.substring(11, 13)
	    		+"-"+titleName.substring(14, 16)));
		acclines[0].getAccvalue(); 
		format.format(new Timestamp(System.currentTimeMillis()));
		//-----
        File templateCopy = new File(fullPath.toString());
        File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
        } catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }
		//Styles for cells
	       WritableCellFormat standartstyle = new WritableCellFormat();
	       standartstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	       standartstyle.setAlignment(Alignment.LEFT);
	       standartstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	       standartstyle.setWrap(true);
		 //style for bold font
	       WritableFont arial10boldfont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
	       WritableCellFormat arial10boldformat = new WritableCellFormat(arial10boldfont);
	       arial10boldformat.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
	       arial10boldformat.setBorder(Border.TOP, BorderLineStyle.MEDIUM);
	       arial10boldformat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
	       arial10boldformat.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
		//style for bold first colunm cells
	     //style for bold and #.## format number cells
	       WritableCellFormat twodpsFormat = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT); 
	       twodpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	       twodpsFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	       twodpsFormat.setWrap(true);
	       // style for standart first colunm cells
	       WritableCellFormat stan1colcell = new WritableCellFormat();
	       stan1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
	       stan1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN);
	       stan1colcell.setBorder(Border.TOP, BorderLineStyle.THIN);
	       stan1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
	       stan1colcell.setAlignment(Alignment.LEFT);
	       stan1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       stan1colcell.setWrap(true);
	       //style for bold first colunm cells
	       WritableCellFormat bold1colcell = new WritableCellFormat(arial10boldfont);
	       bold1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM,Colour.BLACK);
	       bold1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK);
	       bold1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	       bold1colcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	       bold1colcell.setAlignment(Alignment.CENTRE);
	       bold1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       bold1colcell.setWrap(true);
	       //style for standart number cells
	       WritableCellFormat stnumcell = new WritableCellFormat(NumberFormats.FLOAT);
	       stnumcell.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	       stnumcell.setAlignment(Alignment.RIGHT);
	       stnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       stnumcell.setWrap(true);
	       //Style for total cells
	       WritableCellFormat totalcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
	       totalcell.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
	       totalcell.setAlignment(Alignment.RIGHT);
	       totalcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       totalcell.setWrap(true);
	       //Style for header account name cells
	       WritableCellFormat accnamecell = new WritableCellFormat(arial10boldfont);
	       accnamecell.setBorder(Border.TOP, BorderLineStyle.MEDIUM, Colour.BLACK);
	       accnamecell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
	       accnamecell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	       accnamecell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	       accnamecell.setAlignment(Alignment.CENTRE);
	       accnamecell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       accnamecell.setWrap(true);
	       //Style for account name cell
	       WritableCellFormat accname = new WritableCellFormat();
	       accname.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
	       //Style for colunm header cells
	       WritableCellFormat colheadcell = new WritableCellFormat(arial10boldfont);
	       colheadcell.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
	       colheadcell.setAlignment(Alignment.CENTRE);
	       colheadcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       //Style for last colunm header cells
	       WritableCellFormat lastcolheadcell = new WritableCellFormat(arial10boldfont);
	       lastcolheadcell.setAlignment(Alignment.CENTRE);
	       lastcolheadcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	       lastcolheadcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	       lastcolheadcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	       lastcolheadcell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM,Colour.BLACK);
	       //Style for standart last number cell
	       WritableCellFormat lastnumcell = new WritableCellFormat(NumberFormats.FLOAT);
	       lastnumcell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
	       lastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	       lastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	       lastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	       lastnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
	       lastnumcell.setWrap(true);
	       //Style for bold last number colunm cells
	       WritableCellFormat boldlastnumcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
	       boldlastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	       boldlastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	       boldlastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	       boldlastnumcell.setBorder(Border.RIGHT,BorderLineStyle.MEDIUM,Colour.BLACK);
	       //pattern for now date and time
	       Date now = Calendar.getInstance().getTime(); 
	       DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy"); 
	       DateFormat customTimeFormat = new DateFormat("hh:mm:ss");
	       WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
	       WritableCellFormat timeFormat = new WritableCellFormat (customTimeFormat);  
	     //Style for time
	       WritableCellFormat timestyle = new WritableCellFormat(timeFormat);
	       timestyle.setAlignment(Alignment.LEFT);
	       timestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	     //Style for date 
	       WritableCellFormat datestyle = new WritableCellFormat(dateFormat);
	       datestyle.setAlignment(Alignment.LEFT);
	       datestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	       //style for period
	       WritableCellFormat periodstyle = new WritableCellFormat(arial10boldfont);
	       periodstyle.setAlignment(Alignment.CENTRE);

       ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
     //find start cell
       int col = cellStart.getC(); 
       int row = cellStart.getR();
       
        
		AccValue[] sub1lines = getSub1Values(Account_ID);
		AccValue[] sub2lines = null;
		AccValue[] sub3lines = null;
		 /// for date to string
	       String date_From = format.format(Date_From);
	       String date_To = format.format(Date_To);
	      label = new Label(4, 2," c "+" "+date_From+" по "+date_To,periodstyle); sheet.addCell(label);
	      label = new Label(1, 5, acclines[0].getAccvalue(),bold1colcell);sheet.addCell(label);
	      label = new Label(2, 5, acclines[0].getSubname(),twodpsFormat);sheet.addCell(label);

	      int rowcount = row;	      
	      double sumDr1 = 0;
          double sumCr1 = 0;
	      double sumDr2 = 0;
	      double sumCr2 = 0;
	      double sumDr3 = 0;
	      double sumCr3 = 0;
	      for(int i=0; i<sub1lines.length;i++){
	    	  label = new Label(col, rowcount, sub1lines[i].getSubvalue(),bold1colcell); if(Expand1) sheet.addCell(label);
	    	  label = new Label(col+1, rowcount, sub1lines[i].getSubname(),twodpsFormat); if(Expand1) sheet.addCell(label);
	    	  number = new Number(col+2, rowcount, sub1lines[i].getDr1(),twodpsFormat); if(Expand1) sheet.addCell(number);
		      number = new Number(col+3, rowcount, sub1lines[i].getCr1(),twodpsFormat); if(Expand1) sheet.addCell(number);
		      number = new Number(col+4, rowcount, sub1lines[i].getDr2(),twodpsFormat); if(Expand1) sheet.addCell(number);
		      number = new Number(col+5, rowcount, sub1lines[i].getCr2(),twodpsFormat); if(Expand1) sheet.addCell(number);
		      number = new Number(col+6, rowcount, sub1lines[i].getDr3(),twodpsFormat); if(Expand1) sheet.addCell(number);
		      number = new Number(col+7, rowcount, sub1lines[i].getCr3(),boldlastnumcell); if(Expand1) sheet.addCell(number);
		      
		      sumDr1 += sub1lines[i].getDr1();
	          sumCr1 += sub1lines[i].getCr1();
		      sumDr2 += sub1lines[i].getDr2();
		      sumCr2 += sub1lines[i].getCr2();
		      sumDr3 += sub1lines[i].getDr3();
		      sumCr3 += sub1lines[i].getCr3();
		      
		      if(Expand1) rowcount++;
		      sub2lines = getSub2Values(Account_ID, sub1lines[i].getSubkonto1_id());
		      for(int j=0;j<sub2lines.length;j++){
		    	  if(sub2lines[j].getSubkonto2_id()>0){
		      	  label = new Label(col,rowcount,sub2lines[j].getSubvalue(),stan1colcell); if(Expand2) sheet.addCell(label);
		      	  label = new Label(col+1, rowcount, sub2lines[j].getSubname(),standartstyle); if(Expand2) sheet.addCell(label);
			      number = new Number(col+2, rowcount, sub2lines[j].getDr1(),stnumcell); if(Expand2) sheet.addCell(number);
				  number = new Number(col+3, rowcount, sub2lines[j].getCr1(),stnumcell); if(Expand2) sheet.addCell(number);
				  number = new Number(col+4, rowcount, sub2lines[j].getDr2(),stnumcell); if(Expand2) sheet.addCell(number);
				  number = new Number(col+5, rowcount, sub2lines[j].getCr2(),stnumcell); if(Expand2) sheet.addCell(number);
				  number = new Number(col+6, rowcount, sub2lines[j].getDr3(),stnumcell); if(Expand2) sheet.addCell(number);
				  number = new Number(col+7, rowcount, sub2lines[j].getCr3(),lastnumcell); if(Expand2) sheet.addCell(number);
				  if(Expand2) rowcount++;
				  sub3lines = getSub3Values(Account_ID, sub1lines[i].getSubkonto1_id(), sub2lines[j].getSubkonto2_id());
				  for(int z=0;z<sub3lines.length;z++){
				  	  if(sub3lines[z].getSubkonto3_id()>0){
					  label = new Label(col,rowcount,sub3lines[z].getSubvalue(),stan1colcell); if(Expand3) sheet.addCell(label);
				   	  label = new Label(col+1, rowcount, sub3lines[z].getSubname(),standartstyle); if(Expand3) sheet.addCell(label);
					  number = new Number(col+2, rowcount, sub3lines[z].getDr1(),stnumcell); if(Expand3) sheet.addCell(number);
					  number = new Number(col+3, rowcount, sub3lines[z].getCr1(),stnumcell); if(Expand3) sheet.addCell(number);
					  number = new Number(col+4, rowcount, sub3lines[z].getDr2(),stnumcell); if(Expand3) sheet.addCell(number);
					  number = new Number(col+5, rowcount, sub3lines[z].getCr2(),stnumcell); if(Expand3) sheet.addCell(number);
					  number = new Number(col+6, rowcount, sub3lines[z].getDr3(),stnumcell); if(Expand3) sheet.addCell(number);
					  number = new Number(col+7, rowcount, sub3lines[z].getCr3(),lastnumcell); if(Expand3) sheet.addCell(number);
					  if(Expand3) rowcount++;
				  	  }
				  }
		        }
		      }
	      }

	       label = new Label(col, rowcount, "",totalcell); sheet.addCell(label);
	       label = new Label(col+1, rowcount, " Итого: ",totalcell); sheet.addCell(label);
	       number = new Number(col+2, rowcount, 0,totalcell); sheet.addCell(number);
	       number = new Number(col+3, rowcount, 0,totalcell); sheet.addCell(number);
	       if((sumCr1-sumDr1)<0)
	       {
	    	   number = new Number(col+2, rowcount, sumDr1 - sumCr1,totalcell); sheet.addCell(number);
	       }
	       else
	       {
	    	   number = new Number(col+3, rowcount, sumCr1 - sumDr1,totalcell); sheet.addCell(number);	
	       }
	       
	       number = new Number(col+4, rowcount, sumDr2,totalcell); sheet.addCell(number);
	       number = new Number(col+5, rowcount, sumCr2,totalcell); sheet.addCell(number);
	       
	       number = new Number(col+6, rowcount, 0,totalcell); sheet.addCell(number);
	       number = new Number(col+7, rowcount, 0,totalcell); sheet.addCell(number);
	       if((sumCr3 - sumDr3)<0)
	       {
	    	   number = new Number(col+6, rowcount, sumDr3 - sumCr3,totalcell); sheet.addCell(number);
	       }
	       else
	       {
	    	   number = new Number(col+7, rowcount, sumCr3 - sumDr3,totalcell); sheet.addCell(number);
	       }
	       
	      
	      
	      ///current date and time
	     DateTime dateCell = new DateTime(col+6, rowcount+3, now, datestyle); sheet.addCell(dateCell);
	     DateTime timeCell = new DateTime(col+7, rowcount+3, now, timestyle); sheet.addCell(timeCell);
	      //Username
	      MUser u = new MUser(getCtx(),Env.getAD_User_ID(getCtx()), get_TrxName());
	      label = new Label(col+6, rowcount+2, u.getC_BPartner().getName2()); sheet.addCell(label);
	       //write to excel sheet
	       copy.write();
	       copy.close();
	       tableBook.close();
	       temporaryFile.delete();
	       
	    // Open the file with the application associated
			String fileName = fullPath.toString();
			Util.launchFile(new File(fileName));
		//
		return Msg.translate(m_ctx, "Success");
	}
	/*************************************************Start**Account********************************************************************************* */
	private AccValue[] getAccValues(){
		AccValue vacLine = null;
    	List<AccValue> list = new ArrayList<AccValue>();
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			try
			{
				pstmt = DB.prepareStatement(getAccSql(),null);
				pstmt.setTimestamp(1, Date_From);
				pstmt.setTimestamp(2, Date_From);
				pstmt.setTimestamp(3, Date_To);
				pstmt.setInt(4, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(5, Env.getAD_Org_ID(Env.getCtx()));
				if(Account_ID!=0)
					pstmt.setInt(6, Account_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccValue();
					vacLine.setAd_client_id(rs.getInt(1));
					vacLine.setAd_org_id(rs.getInt(2));
					vacLine.setAccount_id(rs.getInt(3));
					vacLine.setAccvalue(rs.getString(4));
					vacLine.setSubname(rs.getString(5));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "Account", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccValue[list.size()]);
	}

	private String getAccSql(){
		StringBuffer Accsql = new StringBuffer();
		Accsql.append(  " WITH main AS(SELECT ad_client_id,ad_org_id,account_id FROM adempiere.fact_acct where dateacct<? \n" + //1
						" GROUP BY ad_client_id,ad_org_id,account_id  UNION ALL \n"+
						" SELECT ad_client_id,ad_org_id,account_id FROM adempiere.fact_acct where dateacct BETWEEN ? AND ? \n" + //2,3
						" GROUP BY ad_client_id,ad_org_id,account_id) \n"+
						" SELECT m.ad_client_id,m.ad_org_id,m.account_id,c.value AS accvalue, c.name as accname FROM main m,adempiere.c_elementvalue c \n"+
						" WHERE c.c_elementvalue_id = m.account_id \n"+
						" AND m.ad_client_id=? \n"+ //4
						" AND m.ad_org_id=? \n"); //5
		if(Account_ID!=0)
			Accsql.append(" AND m.account_id=? \n"); //6
		Accsql.append( " GROUP BY  m.ad_client_id,m.ad_org_id,m.account_id,c.value,c.name ORDER BY  c.value");
		return Accsql.toString();
	}
	/*************************************************End**Account************************************************************************************** */

	/***********************************************Start**Subkonto1************************************************************************************ */
	private AccValue[] getSub1Values(int account_id){
		AccValue vacLine = null;
    	List<AccValue> list = new ArrayList<AccValue>();
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = DB.prepareStatement(getSub1Sql(),null);
			pstmt.setTimestamp(1, Date_From);
			pstmt.setInt(2,account_id);
			pstmt.setTimestamp(3, Date_From);
			pstmt.setTimestamp(4, Date_To);
			pstmt.setInt(5, account_id);
			if(Subkonto1>0)
				pstmt.setInt(6, Subkonto1);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				vacLine = new AccValue();
				
				vacLine.setAd_client_id(rs.getInt(1));
				vacLine.setAd_org_id(rs.getInt(2));
				vacLine.setAccount_id(rs.getInt(3));
				vacLine.setSubkonto1_id(rs.getInt(4));
				vacLine.setAccvalue(rs.getString(5));
				vacLine.setSubvalue(rs.getString(6));
				vacLine.setSubname(rs.getString(7));
				vacLine.setDr1(rs.getDouble(8));
				vacLine.setCr1(rs.getDouble(9));
				vacLine.setDr2(rs.getDouble(10));
				vacLine.setCr2(rs.getDouble(11));
				vacLine.setDr3(rs.getDouble(12));
				vacLine.setCr3(rs.getDouble(13));
				list.add(vacLine);
				vacLine = null;
			}
			
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "Account", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return list.toArray(new AccValue[list.size()]);
	}
	
	private String getSub1Sql(){
		StringBuffer sub1sql = new StringBuffer();
		switch(n){
		case 1:{
			sub1sql.append(" WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n" + //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,bp.value as subvalue,bp.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_bpartner bp ON(m.subkonto1_id = bp.c_bpartner_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); //6-Subkonto1_id
			sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,bp.value,bp.name ORDER BY bp.name,bp.value) " +
						   " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0 ");
			break;}
		case 2:{
			sub1sql.append(" WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n" + //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+  //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,bp.value as subvalue,bp.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_bpartner bp ON(m.subkonto1_id = bp.c_bpartner_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); //6-Subkonto1_id
			sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,bp.value,bp.name ORDER BY bp.name) " +
						   " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0 ");
			break;}
		case 3:{
			sub1sql.append("WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,COALESCE(fi_form3_id,0) as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+ //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_form3_id UNION ALL \n"+ 
					" SELECT ad_client_id,ad_org_id,account_id,COALESCE(fi_form3_id,0) as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_form3_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,form3.fi_code as subvalue,form3.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+ 
					" FROM main m \n"+
					" LEFT JOIN fi_form3 form3 ON(m.subkonto1_id = form3.fi_form3_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,form3.fi_code,form3.name ORDER BY form3.fi_code,form3.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 4:{
			sub1sql.append("WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,typeoftax.fi_code as subvalue,typeoftax.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN fi_typeoftax typeoftax ON(m.subkonto1_id = typeoftax.fi_typeoftax_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,typeoftax.fi_code,typeoftax.name ORDER BY typeoftax.fi_code,typeoftax.name) " +
								   " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
		break;}
		case 5:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,typeoftax.fi_code as subvalue,typeoftax.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN fi_typeoftax typeoftax ON(m.subkonto1_id = typeoftax.fi_typeoftax_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); 
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,typeoftax.fi_code,typeoftax.name ORDER BY typeoftax.fi_code,typeoftax.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0"); 
			break;}
		case 6:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+  //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,chargetype.fi_code as subvalue,COALESCE(chargetype.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_chargetype chargetype ON(m.subkonto1_id = chargetype.c_chargetype_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 AND c.value like '6%' ");
			if(Subkonto1>0)
				sub1sql.append("AND m.subkonto1_id = ? \n"); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,chargetype.fi_code,chargetype.name ORDER BY chargetype.fi_code,chargetype.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 7:{
			sub1sql.append(" WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,chargetype.fi_code as subvalue,COALESCE(chargetype.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_chargetype chargetype ON(m.subkonto1_id = chargetype.c_chargetype_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 AND c.value like '6%' ");
			if(Subkonto1>0)
				sub1sql.append("AND m.subkonto1_id = ? \n"); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,chargetype.fi_code,chargetype.name ORDER BY chargetype.fi_code,chargetype.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 8:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+  //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,  \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,chargetype.fi_code as subvalue,COALESCE(chargetype.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_chargetype chargetype ON(m.subkonto1_id = chargetype.c_chargetype_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 AND c.value like '7%' ");
			if(Subkonto1>0)
				sub1sql.append("AND m.subkonto1_id = ? \n"); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,chargetype.fi_code,chargetype.name ORDER BY chargetype.fi_code,chargetype.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 9:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+  //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,  \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,chargetype.fi_code as subvalue,COALESCE(chargetype.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_chargetype chargetype ON(m.subkonto1_id = chargetype.c_chargetype_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 AND c.value like '7%' ");
			if(Subkonto1>0)
				sub1sql.append("AND m.subkonto1_id = ? \n"); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,chargetype.fi_code,chargetype.name ORDER BY chargetype.fi_code,chargetype.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 10:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,COALESCE(c_chargetype_id,0) as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+  //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,  \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,chargetype.fi_code as subvalue,COALESCE(chargetype.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_chargetype chargetype ON(m.subkonto1_id = chargetype.c_chargetype_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 AND c.value like '7%' ");
			if(Subkonto1>0)
				sub1sql.append("AND m.subkonto1_id = ? \n"); //6-Subkonto1_id
					sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,chargetype.fi_code,chargetype.name ORDER BY chargetype.fi_code,chargetype.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 11:{
			sub1sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n" + //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value as accvalue,reserve.name as subvalue,COALESCE(reserve.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN hrm_reserve reserve ON(m.subkonto1_id = reserve.hrm_reserve_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE 1=1 ");
			if(Subkonto1>0) 
				sub1sql.append(" AND m.subkonto1_id = ? \n "); //6-Subkonto1_id
			sub1sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,reserve.name,reserve.name ORDER BY m.subkonto1_id) " +
					       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;
				}
		}
		return sub1sql.toString();
	}
	
	/*************************************************End**Subkonto1************************************************************************************ */

	/***********************************************Start**Subkonto2************************************************************************************ */
	private AccValue[] getSub2Values(int account_id,int subkonto1_id){
		AccValue vacLine = null;
    	List<AccValue> list = new ArrayList<AccValue>();
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = DB.prepareStatement(getSub2Sql(),null);
			pstmt.setTimestamp(1, Date_From);
			pstmt.setInt(2, account_id);
			pstmt.setTimestamp(3, Date_From);
			pstmt.setTimestamp(4, Date_To);
			pstmt.setInt(5, account_id);
			pstmt.setInt(6, subkonto1_id);
			if(Subkonto2>0)
				pstmt.setInt(7, Subkonto2);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				vacLine = new AccValue();
				vacLine.setAd_client_id(rs.getInt(1));
				vacLine.setAd_org_id(rs.getInt(2));
				vacLine.setAccount_id(rs.getInt(3));
				vacLine.setSubkonto2_id(rs.getInt(4));
				vacLine.setAccvalue(rs.getString(5));
				vacLine.setSubvalue(rs.getString(6));
				vacLine.setSubname(rs.getString(7));
				vacLine.setDr1(rs.getDouble(8));
				vacLine.setCr1(rs.getDouble(9));
				vacLine.setDr2(rs.getDouble(10));
				vacLine.setCr2(rs.getDouble(11));
				vacLine.setDr3(rs.getDouble(12));
				vacLine.setCr3(rs.getDouble(13));
				list.add(vacLine);
				vacLine = null;
			}
			
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "Account", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return list.toArray(new AccValue[list.size()]);
	}
	
	private String getSub2Sql(){
		StringBuffer sub2sql = new StringBuffer();
		
		switch(n){
		case 1:{
			sub2sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id, \n"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1, \n"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3 \n"+
					" FROM v_fi_fact_acct_with_subconto WHERE '01/09/1990'=? AND -1=? AND '01/09/1990'=? AND '01/09/1990'=? AND -1=? AND -1=? \n");
			if(Subkonto2>0)
				sub2sql.append(" AND -1=? ");
			break;}
		case 2:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id,COALESCE(userelement1_id,1000316) as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+ //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id,userelement1_id UNION ALL \n"+ 
					" SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id,COALESCE(userelement1_id,1000316) as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+ //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id,userelement1_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue, \n"+
					" '№ '||cms_contract_contractnum as subvalue," +
					" '№ '||cms_contract_contractnum||COALESCE(' от '||cast(to_char(cms_contract_creatdate,'DD.MM.YYYY') as text),'')||COALESCE(' '||cms.description) as subname, \n"+
					//" cms_contract_contractnum||COALESCE(' от '||cast(to_char(cms_contract_creatdate,'DD.MM.YYYY') as text),'') as subvalue,cms.description as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN cms_contract cms ON(m.subkonto2_id = cms.cms_contract_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE subkonto1_id = ? \n "); //6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND subkonto2_id = ? \n "); //7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value, cms.cms_contract_contractnum,cms_contract_creatdate,cms.description " +
							" ORDER BY cms.cms_contract_contractnum,cms_contract_creatdate) " +
							" SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 3:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,fi_form3_id as subkonto1_id,COALESCE(fi_form3explanation_id,0) as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+ //1-Date_From 2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_form3_id,fi_form3explanation_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,fi_form3_id as subkonto1_id,COALESCE(fi_form3explanation_id,0) as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+ //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_form3_id,fi_form3explanation_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,form3exp.fi_code as subvalue,form3exp.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN fi_form3explanation form3exp ON(m.subkonto2_id = form3exp.fi_form3explanation_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE subkonto1_id = ? \n"); //6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND subkonto2_id = ? \n "); //7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,form3exp.fi_code,form3exp.name ORDER BY form3exp.fi_code,form3exp.name)" +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0 ");
			break;}
		case 4:{
			sub2sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id, \n"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1, \n"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3 \n"+
					" FROM v_fi_fact_acct_with_subconto WHERE '01/09/1990'=? AND -1=? AND '01/09/1990'=? AND '01/09/1990'=? AND -1=? AND -1=? \n");
			if(Subkonto2>0)
				sub2sql.append(" AND -1=? ");  
			break;}
		case 5:{
			sub2sql.append("WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id,c_tax_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id=? \n"+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id,c_tax_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,fi_typeoftax_id as subkonto1_id,c_tax_id as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=? \n"+ //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,fi_typeoftax_id,c_tax_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,tax.fi_code as subvalue,tax.name as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_tax tax ON(m.subkonto2_id = tax.c_tax_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE subkonto1_id = ? \n"); //6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND subkonto2_id = ? \n "); //7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,tax.fi_code,tax.name ORDER BY tax.fi_code,tax.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0"); 
			break;}
		case 6:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+ //1-Date_From 2-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,\n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,charge.fi_code as subvalue,COALESCE(charge.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_charge charge ON(m.subkonto2_id = charge.c_charge_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '6%' AND m.subkonto1_id = ? \n");//6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n");//7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,charge.fi_code,charge.name ORDER BY charge.fi_code,charge.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 7:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+  //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,\n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+ //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,charge.fi_code as subvalue,COALESCE(charge.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_charge charge ON(m.subkonto2_id = charge.c_charge_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '6%' AND m.subkonto1_id = ? \n");//6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n");//7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,charge.fi_code,charge.name ORDER BY charge.fi_code,charge.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 8:{
			sub2sql.append("WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+ //3-Date_From 4-Date_To 5-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,charge.fi_code as subvalue,COALESCE(charge.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_charge charge ON(m.subkonto2_id = charge.c_charge_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '7%' AND m.subkonto1_id = ? \n");//6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n");//7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,charge.fi_code,charge.name ORDER BY charge.fi_code,charge.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0"); 
			break;}
		case 9:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+ //1-Date_From 2-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,charge.fi_code as subvalue,COALESCE(charge.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_charge charge ON(m.subkonto2_id = charge.c_charge_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '7%' AND m.subkonto1_id = ? \n");//6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n");//7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,charge.fi_code,charge.name ORDER BY charge.fi_code,charge.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 10:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+  //1-Date_From 2-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,charge.fi_code as subvalue,COALESCE(charge.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_charge charge ON(m.subkonto2_id = charge.c_charge_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '7%' AND m.subkonto1_id = ? \n");//6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n");//7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,charge.fi_code,charge.name ORDER BY charge.fi_code,charge.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 11:{
			sub2sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id,hrm_reservesectional_id as subkonto2_id,\n "+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2\n "+ 
					" FROM v_fi_fact_acct_with_subconto\n "+
					" WHERE dateacct<? AND account_id=?\n "+ //1-Date_From 2-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id,hrm_reservesectional_id UNION ALL\n "+
					" SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id,hrm_reservesectional_id as subkonto2_id,\n "+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2\n "+ 
					" FROM v_fi_fact_acct_with_subconto\n "+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=?\n "+ //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id,hrm_reservesectional_id)\n "+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value as accvalue,reservesectional.name as subvalue,COALESCE(reservesectional.name,'<...>') as subname,\n "+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2,\n "+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3\n "+
					" FROM main m \n "+
					" LEFT JOIN hrm_reservesectional reservesectional ON(m.subkonto2_id = reservesectional.hrm_reservesectional_id)\n "+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id)\n "+
					" WHERE m.subkonto1_id=?\n "); //6-Subkonto1_id
			if(Subkonto2>0)
				sub2sql.append(" AND m.subkonto2_id = ? \n "); //7-Subkonto2_id
					sub2sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto2_id,c.value,reservesectional.name,reservesectional.name ORDER BY m.subkonto2_id) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		}
		return sub2sql.toString();
	}

	/************************************************End**Subconto2************************************************************************************/
	
	/**********************************************Start**Subkonto3**************************************************************************************/
	/***********************************************End***Subkonto2************************************************************************************ */
	
	/**********************************************Start**Subkonto3************************************************************************************ */
	private AccValue[] getSub3Values(int account_id, int subkonto1_id, int subkonto2_id){
		
		AccValue vacLine = null;
    	List<AccValue> list = new ArrayList<AccValue>();
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = DB.prepareStatement(getSub3Sql(),null);
			pstmt.setTimestamp(1, Date_From);
			pstmt.setInt(2, account_id);
			pstmt.setTimestamp(3, Date_From);
			pstmt.setTimestamp(4, Date_To);
			pstmt.setInt(5, account_id);
			pstmt.setInt(6, subkonto1_id);
			pstmt.setInt(7, subkonto2_id);
			if(Subkonto3>0)
				pstmt.setInt(8, Subkonto3);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				vacLine = new AccValue();
				vacLine.setAd_client_id(rs.getInt(1));
				vacLine.setAd_org_id(rs.getInt(2));
				vacLine.setAccount_id(rs.getInt(3));
				vacLine.setSubkonto3_id(rs.getInt(4));
				vacLine.setAccvalue(rs.getString(5));
				vacLine.setSubvalue(rs.getString(6));
				vacLine.setSubname(rs.getString(7));
				vacLine.setDr1(rs.getDouble(8));
				vacLine.setCr1(rs.getDouble(9));
				vacLine.setDr2(rs.getDouble(10));
				vacLine.setCr2(rs.getDouble(11));
				vacLine.setDr3(rs.getDouble(12));
				vacLine.setCr3(rs.getDouble(13));
				list.add(vacLine);
				vacLine = null;
			}
			
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "Account", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return list.toArray(new AccValue[list.size()]);
	}
	
	
	private String getSub3Sql(){
		StringBuffer sub3sql = new StringBuffer();
		switch(n){
		case 1:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 2:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 3:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 4:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 5:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 6:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 7:{
			sub3sql.append(" WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,c_bpartner_id as subkonto3_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+  //1-Date_From  2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,c_bpartner_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,c_bpartner_id as subkonto3_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,c_bpartner_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value as accvalue,bp.value as subvalue,COALESCE(bp.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_bpartner bp ON(m.subkonto3_id = bp.c_bpartner_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '6%' AND m.subkonto1_id = ? AND m.subkonto2_id = ? \n"); //6-Subkonto1_id 7-Subkonto2_id
			if(Subkonto3>0)
				sub3sql.append(" AND m.subkonto3_id = ? \n"); //8-Subkonto3_id
					sub3sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value,bp.value,bp.name ORDER BY bp.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 8:{
			sub3sql.append(" SELECT null::numeric AS ad_client_id,null::numeric AS ad_org_id,null::numeric AS account_id, null::numeric AS subkonto3_id,"+
					" null::character as accvalue,null::character as subvalue,null::character as subname, null::numeric(10,2) AS dr1,null::numeric(10,2) AS cr1,"+ 
					" null::numeric(10,2) AS dr2,null::numeric(10,2) AS cr2, null::numeric(10,2) AS dr3,null::numeric(10,2) AS cr3"+ 
					" FROM v_fi_fact_acct_with_subconto WHERE '01/01/1990'=? AND -1=? AND '01/01/1990'=? AND '01/01/1990'=? AND -1=? AND -1=? AND -1=? ");
			if(Subkonto3>0)
				sub3sql.append(" AND -1=? ");
			break;}
		case 9:{
			sub3sql.append(" WITH pMain AS ( WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,c_bpartner_id as subkonto3_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+  //1-Date_From  2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,c_bpartner_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,c_bpartner_id as subkonto3_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,c_bpartner_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value as accvalue,bp.value as subvalue,COALESCE(bp.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN c_bpartner bp ON(m.subkonto3_id = bp.c_bpartner_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '7%' AND m.subkonto1_id = ? AND m.subkonto2_id = ? \n");//6-Subkonto1_id 7-Subkonto2_id
			if(Subkonto3>0)
				sub3sql.append(" AND m.subkonto3_id = ? \n");//8-Subkonto3_id
					sub3sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value,bp.value,bp.name ORDER BY bp.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 10:{
			sub3sql.append(" WITH pMain AS (WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,hr_department_id as subkonto3_id, \n"+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+
					" WHERE dateacct<? AND account_id = ? \n"+  //1-Date_From  2-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,hr_department_id UNION ALL \n"+
					" SELECT ad_client_id,ad_org_id,account_id,c_chargetype_id as subkonto1_id,c_charge_id as subkonto2_id,hr_department_id as subkonto3_id, \n"+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n"+ 
					" FROM v_fi_fact_acct_with_subconto \n"+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id = ? \n"+  //3-Date_From 4-Date_To 5-Account_ID
					" GROUP BY ad_client_id,ad_org_id,account_id,c_chargetype_id,c_charge_id,hr_department_id) \n"+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value as accvalue,dep.value as subvalue,COALESCE(dep.name,'<...>') as subname, \n"+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2, \n"+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n"+
					" FROM main m \n"+
					" LEFT JOIN hr_department dep ON(m.subkonto3_id = dep.hr_department_id) \n"+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n"+
					" WHERE c.value like '7%' AND m.subkonto1_id = ? AND m.subkonto2_id = ? \n");//6-Subkonto1_id 7-Subkonto2_id
			if(Subkonto3>0)
				sub3sql.append(" AND m.subkonto3_id = ? \n");//8-Subkonto3_id
					sub3sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value,dep.value,dep.name ORDER BY dep.value,dep.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		case 11:{
			sub3sql.append("WITH pMain AS ( WITH main AS ( "+
					" SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id,hrm_reservesectional_id as subkonto2_id,c_bpartner_id as subkonto3_id,\n "+
					" SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2\n "+ 
					" FROM v_fi_fact_acct_with_subconto\n "+
					" WHERE dateacct<? AND account_id=?\n "+ //1-Date_From  2-Account_ID  
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id,hrm_reservesectional_id,c_bpartner_id UNION ALL\n "+
					" SELECT ad_client_id,ad_org_id,account_id,hrm_reserve_id as subkonto1_id,hrm_reservesectional_id as subkonto2_id,c_bpartner_id as subkonto3_id,\n "+
					" 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2\n "+ 
					" FROM v_fi_fact_acct_with_subconto\n "+ 
					" WHERE dateacct BETWEEN ? AND ? AND account_id=?\n "+ //3-Date_From 4-Date_To 5-Account_ID 
					" GROUP BY ad_client_id,ad_org_id,account_id,hrm_reserve_id,hrm_reservesectional_id,c_bpartner_id)\n "+
					" SELECT m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value as accvalue,bp.value as subvalue,COALESCE(bp.name,'<...>') as subname,\n "+
					" adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1, SUM(dr2) as dr2, SUM(cr2) as cr2,\n "+
					" adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3,adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3\n "+
					" FROM main m\n "+
					" LEFT JOIN c_bpartner bp ON(m.subkonto3_id = bp.c_bpartner_id)\n "+
					" LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id)\n "+
					" WHERE m.subkonto1_id=? AND m.subkonto2_id = ?\n ");//6-Subkonto1_id 7-Subkonto2_id
			if(Subkonto3>0)
				sub3sql.append(" AND m.subkonto3_id = ? \n");//8-Subkonto3_id
					sub3sql.append(" GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto3_id,c.value,bp.value,bp.name ORDER BY bp.name) " +
							       " SELECT * FROM pMain WHERE dr1 > 0 OR cr1 > 0 OR dr2 > 0 OR cr2 > 0 OR dr3 > 0 OR cr3 > 0");
			break;}
		
		}
		return sub3sql.toString();
	}
	/************************************************End**Subkonto3************************************************************************************ */
	private class AccValue{
		
		private int ad_client_id;
		private int ad_org_id;
		private int account_id;
		private int subkonto1_id;
		private int subkonto2_id;
		private int subkonto3_id;
		private String accvalue;
		private String subvalue;
		private String subname;
		private double dr1;
		private double cr1;
		private double dr2;
		private double cr2;
		private double dr3;
		private double cr3;
		
		public int getAd_client_id() {
			return ad_client_id;
		}
		public void setAd_client_id(int ad_client_id) {
			this.ad_client_id = ad_client_id;
		}
		public int getAd_org_id() {
			return ad_org_id;
		}
		public void setAd_org_id(int ad_org_id) {
			this.ad_org_id = ad_org_id;
		}
		public int getAccount_id() {
			return account_id;
		}
		public void setAccount_id(int account_id) {
			this.account_id = account_id;
		}
		public int getSubkonto1_id() {
			return subkonto1_id;
		}
		public void setSubkonto1_id(int subkonto1_id) {
			this.subkonto1_id = subkonto1_id;
		}
		public int getSubkonto2_id() {
			return subkonto2_id;
		}
		public void setSubkonto2_id(int subkonto2_id) {
			this.subkonto2_id = subkonto2_id;
		}
		public int getSubkonto3_id() {
			return subkonto3_id;
		}
		public void setSubkonto3_id(int subkonto3_id) {
			this.subkonto3_id = subkonto3_id;
		}
		public String getAccvalue() {
			return accvalue;
		}
		public void setAccvalue(String accvalue) {
			this.accvalue = accvalue;
		}
		public String getSubvalue() {
			return subvalue;
		}
		public void setSubvalue(String subvalue) {
			this.subvalue = subvalue;
		}
		public String getSubname() {
			return subname;
		}
		public void setSubname(String subname) {
			this.subname = subname;
		}
		public double getDr1() {
			return dr1;
		}
		public void setDr1(double dr1) {
			this.dr1 = dr1;
		}
		public double getCr1() {
			return cr1;
		}
		public void setCr1(double cr1) {
			this.cr1 = cr1;
		}
		public double getDr2() {
			return dr2;
		}
		public void setDr2(double dr2) {
			this.dr2 = dr2;
		}
		public double getCr2() {
			return cr2;
		}
		public void setCr2(double cr2) {
			this.cr2 = cr2;
		}
		public double getDr3() {
			return dr3;
		}
		public void setDr3(double dr3) {
			this.dr3 = dr3;
		}
		public double getCr3() {
			return cr3;
		}
		public void setCr3(double cr3) {
			this.cr3 = cr3;
		}
		
		
	}
	protected String getXlsName(String reportName) throws JRException {
		String Result = null;
		String tmpName = reportName;
		Integer i = 0;
		
		File d = new File(Util.localFilePath);
        String[] lF = d.list(new WildcardFileFilter(tmpName+"*.xls"));
        
        for(int j=0; j < lF.length; j++) {
        	File tmp = new File(lF[j]);
        	tmp.delete();
        }
        
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + tmpName+".xls");
			try { 
				if (f.exists() && !f.delete()) {
					tmpName = reportName + "(" +(++i).toString() + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				tmpName = reportName + "(" +(++i).toString() + ")";
			}
		}
		Result = Util.localFilePath + tmpName+".xls";
		return Result;
	}

}
